package com.example.managemyself.api.repository;

import com.example.managemyself.api.entity.Task;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 任务数据访问层
 */
@Repository
public interface TaskRepository extends JpaRepository<Task, Long> {
    
    /**
     * 根据用户ID查找任务
     */
    List<Task> findByUserId(Long userId);
    
    /**
     * 根据用户ID分页查找任务
     */
    Page<Task> findByUserId(Long userId, Pageable pageable);
    
    /**
     * 根据用户ID和状态查找任务
     */
    List<Task> findByUserIdAndStatus(Long userId, Task.TaskStatus status);
    
    /**
     * 根据用户ID和状态分页查找任务
     */
    Page<Task> findByUserIdAndStatus(Long userId, Task.TaskStatus status, Pageable pageable);
    
    /**
     * 根据用户ID和优先级查找任务
     */
    List<Task> findByUserIdAndPriority(Long userId, Task.Priority priority);
    
    /**
     * 根据状态查找任务
     */
    List<Task> findByStatus(Task.TaskStatus status);
    
    /**
     * 根据优先级查找任务
     */
    List<Task> findByPriority(Task.Priority priority);
    
    /**
     * 查找逾期任务
     */
    @Query("SELECT t FROM Task t WHERE t.dueDate < :currentTime AND t.status != :completedStatus")
    List<Task> findOverdueTasks(@Param("currentTime") LocalDateTime currentTime, 
                               @Param("completedStatus") Task.TaskStatus completedStatus);
    
    /**
     * 查找用户的逾期任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND t.dueDate < :currentTime AND t.status != :completedStatus")
    List<Task> findOverdueTasksByUserId(@Param("userId") Long userId, 
                                       @Param("currentTime") LocalDateTime currentTime,
                                       @Param("completedStatus") Task.TaskStatus completedStatus);
    
    /**
     * 查找今日到期的任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND DATE(t.dueDate) = DATE(:today) AND t.status != :completedStatus")
    List<Task> findTasksDueToday(@Param("userId") Long userId, 
                                @Param("today") LocalDateTime today,
                                @Param("completedStatus") Task.TaskStatus completedStatus);
    
    /**
     * 查找本周到期的任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND t.dueDate BETWEEN :weekStart AND :weekEnd AND t.status != :completedStatus")
    List<Task> findTasksDueThisWeek(@Param("userId") Long userId,
                                   @Param("weekStart") LocalDateTime weekStart,
                                   @Param("weekEnd") LocalDateTime weekEnd,
                                   @Param("completedStatus") Task.TaskStatus completedStatus);
    
    /**
     * 模糊搜索任务（标题和描述）
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND (t.title LIKE %:keyword% OR t.description LIKE %:keyword%)")
    Page<Task> searchTasksByUser(@Param("userId") Long userId, @Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据标签搜索任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND t.tags LIKE %:tag%")
    List<Task> findTasksByTag(@Param("userId") Long userId, @Param("tag") String tag);
    
    /**
     * 统计用户任务数量按状态
     */
    @Query("SELECT t.status, COUNT(t) FROM Task t WHERE t.userId = :userId GROUP BY t.status")
    List<Object[]> countTasksByStatus(@Param("userId") Long userId);
    
    /**
     * 统计用户任务数量按优先级
     */
    @Query("SELECT t.priority, COUNT(t) FROM Task t WHERE t.userId = :userId GROUP BY t.priority")
    List<Object[]> countTasksByPriority(@Param("userId") Long userId);
    
    /**
     * 统计用户完成任务数量（按日期）
     */
    @Query("SELECT DATE(t.completedAt) as date, COUNT(t) as count FROM Task t " +
           "WHERE t.userId = :userId AND t.completedAt >= :startDate " +
           "GROUP BY DATE(t.completedAt) ORDER BY DATE(t.completedAt)")
    List<Object[]> getTaskCompletionStats(@Param("userId") Long userId, @Param("startDate") LocalDateTime startDate);
    
    /**
     * 查找用户在指定时间范围内创建的任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND t.createdAt BETWEEN :startDate AND :endDate ORDER BY t.createdAt DESC")
    List<Task> findTasksCreatedBetween(@Param("userId") Long userId,
                                      @Param("startDate") LocalDateTime startDate,
                                      @Param("endDate") LocalDateTime endDate);
    
    /**
     * 查找用户在指定时间范围内完成的任务
     */
    @Query("SELECT t FROM Task t WHERE t.userId = :userId AND t.completedAt BETWEEN :startDate AND :endDate ORDER BY t.completedAt DESC")
    List<Task> findTasksCompletedBetween(@Param("userId") Long userId,
                                        @Param("startDate") LocalDateTime startDate,
                                        @Param("endDate") LocalDateTime endDate);
    
    /**
     * 批量更新任务状态
     */
    @Modifying
    @Transactional
    @Query("UPDATE Task t SET t.status = :status WHERE t.id IN :taskIds")
    void updateTaskStatus(@Param("taskIds") List<Long> taskIds, @Param("status") Task.TaskStatus status);
    
    /**
     * 批量删除用户的任务
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM Task t WHERE t.userId = :userId AND t.status = :status")
    void deleteTasksByUserAndStatus(@Param("userId") Long userId, @Param("status") Task.TaskStatus status);
    
    /**
     * 删除长时间未更新的已取消任务
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM Task t WHERE t.status = :cancelledStatus AND t.updatedAt < :cutoffDate")
    void deleteOldCancelledTasks(@Param("cancelledStatus") Task.TaskStatus cancelledStatus, 
                                @Param("cutoffDate") LocalDateTime cutoffDate);
    
    /**
     * 获取用户任务完成率
     */
    @Query("SELECT " +
           "COUNT(CASE WHEN t.status = :completedStatus THEN 1 END) * 100.0 / COUNT(t) as completionRate " +
           "FROM Task t WHERE t.userId = :userId")
    Double getTaskCompletionRate(@Param("userId") Long userId, @Param("completedStatus") Task.TaskStatus completedStatus);
    
    /**
     * 获取高优先级未完成任务数量
     */
    @Query("SELECT COUNT(t) FROM Task t WHERE t.userId = :userId AND t.priority IN :highPriorities AND t.status != :completedStatus")
    Long countHighPriorityIncompleteTasks(@Param("userId") Long userId, 
                                         @Param("highPriorities") List<Task.Priority> highPriorities,
                                         @Param("completedStatus") Task.TaskStatus completedStatus);
} 